# -*- coding:utf-8 -*-
from com.zzh.utils.dbUtils import MysqlUtils
from com.zzh.utils.jsonUtils_v2.table2Json_Chen import Table2Json
from com.zzh.utils.pagingUtils import MyPaging
from com.zzh.utils.sqlUtils.SqlUtils import SqlUtils
from com.zzh.utils.requestFieldMapDBField import fieldMap_Chen
from com.zzh.bigSearch.simsearch import Getsimwords



class ViewAllItemsDao(object):
    def __init__(self):
        self.conn = MysqlUtils.get_mysql_connections_v2()

    def find_by_condition(self, **kwargs):
        """
        view_item 条件查询
        :param kwargs:
        :return:
        """
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.viewAllItems
        whereSentence = SqlUtils.getOption(conditions, fieldMap)

        cur = self.conn.cursor()
        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "SELECT * FROM view_all_items WHERE " + whereSentence + " and check_status_one=2 AND check_status_two=2 AND item_status=2 ORDER BY " + sortRule
        else:
            sql = "SELECT * FROM view_all_items WHERE " + whereSentence
        try:
            rs = cur.execute(sql)
        except:
            return -200, 0  # sql 语句执行错误

        if rs:
            adinfo = cur.fetchall()
            lists = MyPaging().getPage(rs, pageIndex, pageSize, adinfo)
            adJson = Table2Json.view_all_items_to_json(lists)
            return adJson, rs
        else:
            return None, 0

    def itemBigSearchAndCondition0(self, **kwargs):
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']
        keyword = kwargs['keyword']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.viewAllItems
        whereSentence = SqlUtils.getOption(conditions, fieldMap)
        cur = self.conn.cursor()
        try:
            simKeyword = Getsimwords(keyword)
        except:
            return None, 0
        # print simKeyword[0][0], simKeyword[1][0], simKeyword [2][0]
        word = '%' + keyword + '%'
        word0 = '%' + simKeyword[0][0] + '%'
        word1 = '%' + simKeyword[1][0] + '%'
        word2 = '%' + simKeyword[2][0] + '%'
        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "select * from view_all_items WHERE "+whereSentence+" AND item_id in (SELECT item_id FROM view_all_items WHERE item_title LIKE '"+word+"' OR item_handlername LIKE '"+word+"' OR item_content LIKE '"+word+"' OR industry_name LIKE '"+word+"' OR cat_name LIKE '"+word+"' OR " \
                  "item_title LIKE '"+word0+"' OR item_handlername LIKE '"+word0+"' OR item_content LIKE '"+word0+"' OR industry_name LIKE '"+word0+"' OR cat_name LIKE '"+word0+"' OR " \
                  "item_title LIKE '"+word1+"' OR item_handlername LIKE '"+word1+"' OR item_content LIKE '"+word1+"' OR industry_name LIKE '"+word1+"' OR cat_name LIKE '"+word1+"' OR " \
                  "item_title LIKE '"+word2+"' OR item_handlername LIKE '"+word2+"' OR item_content LIKE '"+word2+"' OR industry_name LIKE '"+word2+"' OR cat_name LIKE '"+word2+"' " \
                  " ) " + " ORDER BY " + sortRule
        else:
            sql = "select * from view_all_items WHERE "+whereSentence+" AND item_id in (SELECT item_id FROM view_all_items WHERE item_title LIKE '"+word+"' OR item_handlername LIKE '"+word+"' OR item_content LIKE '"+word+"' OR industry_name LIKE '"+word+"' OR cat_name LIKE '"+word+"' OR " \
                  "item_title LIKE '"+word0+"' OR item_handlername LIKE '"+word0+"' OR item_content LIKE '"+word0+"' OR industry_name LIKE '"+word0+"' OR cat_name LIKE '"+word0+"' OR " \
                  "item_title LIKE '"+word1+"' OR item_handlername LIKE '"+word1+"' OR item_content LIKE '"+word1+"' OR industry_name LIKE '"+word1+"' OR cat_name LIKE '"+word1+"' OR " \
                  "item_title LIKE '"+word2+"' OR item_handlername LIKE '"+word2+"' OR item_content LIKE '"+word2+"' OR industry_name LIKE '"+word2+"' OR cat_name LIKE '"+word2+"' " \
                  " ) "

        try:
            rs = cur.execute(sql)
        except:
             return -200, 0
        rsJson = None
        if rs:
            rsList = cur.fetchall()
            rsList = MyPaging().getPage(rs, pageIndex, pageSize, rsList)
            rsJson = Table2Json.view_all_items_to_json(rsList)
        cur.close()
        self.conn.close()
        return rsJson, rs

    def itemBigSearchAndCondition1(self, **kwargs):
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']
        keyword = kwargs['keyword']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.viewAllItems
        whereSentence = SqlUtils.getOption(conditions, fieldMap)
        cur = self.conn.cursor()
        # print simKeyword[0][0], simKeyword[1][0], simKeyword[2][0]
        word = keyword
        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "select * from view_all_items WHERE industry_name LIKE %s " \
                  " AND " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "select * from view_all_items WHERE industry_name LIKE %s " \
                  " AND " + whereSentence

        try:
            rs = cur.execute(sql, word)
        except:
            return -200, 0
        rsJson = None
        if rs:
            rsList = cur.fetchall()
            rsList = MyPaging().getPage(rs, pageIndex, pageSize, rsList)
            rsJson = Table2Json.view_all_items_to_json(rsList)
        cur.close()
        self.conn.close()
        return rsJson, rs

    def itemBigSearchAndCondition2(self, **kwargs):
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']
        keyword = kwargs['keyword']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.viewAllItems
        whereSentence = SqlUtils.getOption(conditions, fieldMap)
        cur = self.conn.cursor()
        word=keyword

        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "select * from view_all_items WHERE cat_name LIKE %s " \
                  " AND " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "select * from view_all_items WHERE cat_name LIKE %s " \
                  " AND " + whereSentence

        try:
            rs = cur.execute(sql, word)
        except:
            return -200, 0
        rsJson = None
        if rs:
            rsList = cur.fetchall()
            rsList = MyPaging().getPage(rs, pageIndex, pageSize, rsList)
            rsJson = Table2Json.view_all_items_to_json(rsList)
        cur.close()
        self.conn.close()
        return rsJson, rs

    def itemBigSearchAndCondition3(self, **kwargs):
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']
        keyword = kwargs['keyword']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.viewAllItems
        whereSentence = SqlUtils.getOption(conditions, fieldMap)
        cur = self.conn.cursor()
        word = keyword

        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "select * from view_all_items WHERE item_handlername LIKE %s " \
                  " AND " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "select * from view_all_items WHERE item_handlername LIKE %s " \
                  " AND " + whereSentence


        try:
            rs = cur.execute(sql, word)
        except:
            return -200, 0
        rsJson = None
        if rs:
            rsList = cur.fetchall()
            rsList = MyPaging().getPage(rs, pageIndex, pageSize, rsList)
            rsJson = Table2Json.view_all_items_to_json(rsList)
        cur.close()
        self.conn.close()
        return rsJson, rs

    def itemBigSearchAndCondition4(self, **kwargs):
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']
        keyword = kwargs['keyword']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.viewAllItems
        whereSentence = SqlUtils.getOption(conditions, fieldMap)
        cur = self.conn.cursor()
        try:
            simKeyword = Getsimwords(keyword)
        except:
            return None, 0
        word = '%' + keyword + '%'
        word0 = '%' + simKeyword[0][0] + '%'
        word1 = '%' + simKeyword[1][0] + '%'
        word2 = '%' + simKeyword[2][0] + '%'

        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "select * from view_all_items WHERE "+whereSentence+" AND item_id in (SELECT item_id FROM view_all_items WHERE item_title LIKE %s OR item_content LIKE %s OR " \
                  "item_title LIKE %s OR item_content LIKE %s  OR " \
                  "item_title LIKE %s OR item_content LIKE %s  OR " \
                  "item_title LIKE %s OR item_content LIKE %s )" + " ORDER BY " + sortRule
        else:
            sql = "select * from view_all_items WHERE "+whereSentence+" AND item_id in (SELECT item_id FROM view_all_items WHERE item_title LIKE %s OR item_content LIKE %s OR " \
                  "item_title LIKE %s OR item_content LIKE %s  OR " \
                  "item_title LIKE %s OR item_content LIKE %s  OR " \
                  "item_title LIKE %s OR item_content LIKE %s )"
        try:
            rs = cur.execute(sql, [word,word,word0,word0,word1,word1,word2,word2])
        except:
            return None, 0
        rsJson = None
        if rs:
            rsList = cur.fetchall()
            rsList = MyPaging().getPage(rs, pageIndex, pageSize, rsList)
            rsJson = Table2Json.view_all_items_to_json(rsList)
        cur.close()
        self.conn.close()
        return rsJson, rs

    def itemBigSearchAndCondition5(self, **kwargs):
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']
        keyword = kwargs['keyword']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.viewAllItems
        whereSentence = SqlUtils.getOption(conditions, fieldMap)
        cur = self.conn.cursor()

        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "select * from view_all_items WHERE item_title LIKE %s OR item_handlername LIKE %s OR item_content LIKE %s OR industry_name LIKE %s OR cat_name LIKE %s " \
                  " AND " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "select * from view_all_items WHERE item_title LIKE %s OR item_handlername LIKE %s OR item_content LIKE %s OR industry_name LIKE %s OR cat_name LIKE %s " \
                  " AND " + whereSentence
        try:
            rs = cur.execute(sql, [keyword,keyword,keyword,keyword,keyword])
        except:
            return -200, 0
        rsJson = None
        if rs:
            rsList = cur.fetchall()
            rsList = MyPaging().getPage(rs, pageIndex, pageSize, rsList)
            rsJson = Table2Json.view_all_items_to_json(rsList)
        cur.close()
        self.conn.close()
        return rsJson, rs

    def itemBigSearchAndCondition6(self, **kwargs):
        pageIndex = kwargs['pageIndex']
        pageSize = kwargs['pageSize']
        conditions = kwargs['conditions']
        multiSort = kwargs['multiSort']
        keyword = kwargs['keyword']

        # 获取对应的映射字典
        fieldMap = fieldMap_Chen.viewAllItems
        whereSentence = SqlUtils.getOption(conditions, fieldMap)
        cur = self.conn.cursor()

        if multiSort:
            # 排序拼接
            sortRule = SqlUtils.getSortRule(multiSort, fieldMap)
            sql = "select * from view_all_items WHERE item_title LIKE %s OR item_content LIKE %s" \
                  " AND " + whereSentence + " ORDER BY " + sortRule
        else:
            sql = "select * from view_all_items WHERE item_title LIKE %s OR item_content LIKE %s" \
                  " AND " + whereSentence

        try:
            rs = cur.execute(sql, [keyword,keyword])
        except:
            return -200, 0
        rsJson = None
        if rs:
            rsList = cur.fetchall()
            rsList = MyPaging().getPage(rs, pageIndex, pageSize, rsList)
            rsJson = Table2Json.view_all_items_to_json(rsList)
        cur.close()
        self.conn.close()
        return rsJson, rs